package com.coalmine.api.service.impl;

import com.coalmine.api.domain.ApiDatasource;
import com.coalmine.api.domain.DataTypeDict;
import com.coalmine.api.domain.resp.ListHeaderRespVO;
import com.coalmine.api.domain.resp.ListRowsRespVO;
import com.coalmine.api.mapper.ApiDataTypeDictMapper;
import com.coalmine.api.util.JdbcUtil;
import com.coalmine.api.util.ReflectUtil;
import com.coalmine.common.utils.StringUtils;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class ApiHistoryDataServiceImpl {

    private static final String ORDER_COLUMNS_SPLIT_TOKEN = ",";

    @Autowired
    private ApiDataTypeDictMapper dictMapper;

    @Autowired
    private ApiDatasourceServiceImpl datasourceService;

    public List<DataTypeDict> getDict() {
        return dictMapper.list();
    }

    public Object getHeader(ApiDatasource datasource, String tableName) throws Exception {
        String sql = null;
        String database = JdbcUtil.parseJDBCUrl(datasource.getUrl()).getDbName();
        if ("mysql".equalsIgnoreCase(datasource.getType())) {
            sql = String.format(
                    "SELECT column_name, column_comment\n" +
                    "  FROM information_schema.`columns` \n" +
                    " WHERE table_schema='%s'\n" +
                    "   AND table_name = '%s'\n" +
                    " ORDER BY ordinal_position;", database, tableName);
        }
        if ("clickhouse".equalsIgnoreCase(datasource.getType())) {
            sql = String.format("" +
                    "SELECT name as column_name, comment as column_comment\n" +
                    "  FROM `system`.columns\n" +
                    " WHERE `database`='%s'\n" +
                    "   AND `table`='%s' \n" +
                    " ORDER BY `position`", database, tableName);
        }
        if (StringUtils.isNull(sql)) {
            throw new RuntimeException(String.format("不支持的数据源类型: %s", datasource.getType()));
        }
        Connection conn = getConnect(datasource);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        List<Map<String, Object>> propList = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> properties = Maps.newHashMap();
            String columnName = rs.getString("column_name").trim().replace(".", "");
            String columnRemark = rs.getString("column_comment");
            if (columnRemark == "" || columnRemark == null || columnRemark.length() == 0) {
                properties.put("name", columnName);
            } else {
                properties.put("name", columnRemark);
            }
            properties.put("value", columnName.toLowerCase());
            propList.add(properties);
        }
        close(conn, statement, rs);

        List<ListHeaderRespVO> headers = new ArrayList<>();
        if (propList.size() > 0) {
            for (Map<String, Object> prop: propList) {
                ListHeaderRespVO header = new ListHeaderRespVO();
                headers.add((ListHeaderRespVO) ReflectUtil.getTarget(header, prop));
            }
        }
        return headers;
    }

    public Map<String, String> getColumnName(ApiDatasource datasource, String tableName) throws Exception {
        String sql = null;
        String database = JdbcUtil.parseJDBCUrl(datasource.getUrl()).getDbName();
        if ("mysql".equalsIgnoreCase(datasource.getType())) {
            sql = String.format(
                    "SELECT column_name, column_comment\n" +
                            "  FROM information_schema.`columns` \n" +
                            " WHERE table_schema='%s'\n" +
                            "   AND table_name = '%s'\n" +
                            " ORDER BY ordinal_position;", database, tableName);
        }
        if ("clickhouse".equalsIgnoreCase(datasource.getType())) {
            sql = String.format("" +
                    "SELECT name as column_name, comment as column_comment\n" +
                    "  FROM `system`.columns\n" +
                    " WHERE `database`='%s'\n" +
                    "   AND `table`='%s' \n" +
                    " ORDER BY `position`", database, tableName);
        }
        if (StringUtils.isNull(sql)) {
            throw new RuntimeException(String.format("不支持的数据源类型: %s", datasource.getType()));
        }
        Connection conn = getConnect(datasource);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        Map<String, String> columnMap = new HashMap<>();
        while (rs.next()) {
            String columnName = rs.getString("column_name").trim().replace(".", "");
            String columnRemark = rs.getString("column_comment");
            columnMap.put(columnName, columnRemark);
        }
        close(conn, statement, rs);
        return columnMap;
    }

    public long getTotal(ApiDatasource datasource, String tableName) throws Exception {
        String sql = String.format("SELECT count(*) FROM %s;", tableName);
        Connection conn = getConnect(datasource);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        long total = 0;
        if (rs.next()) {
            total = rs.getLong(1);
        }
        close(conn, statement, rs);

        return total;
    }

    public Object getRows(ApiDatasource datasource, String tableName, int pageNum, int pageSize) throws Exception {
        int offSet = (pageNum - 1) * pageSize;

        String sql;

        Map<String, String> columnMap = getColumnName(datasource, tableName);
        String orders = getOrders(datasource.getId(), tableName, columnMap);
        if (StringUtils.isNull(orders)) {
            sql = String.format(
                    "SELECT * FROM %s " +
                            "LIMIT %s OFFSET %s;", tableName, pageSize, offSet);
        } else {
            sql = String.format(
                    "SELECT * FROM %s " +
                            "ORDER BY %s " +
                            "LIMIT %s OFFSET %s;", tableName, orders, pageSize, offSet);
        }

        Connection conn = getConnect(datasource);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        List<Map<String, Object>> propList = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> properties = Maps.newHashMap();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                if (rs.getString(i) == null || rs.getString(i) == "") {
                    properties.put(metaData.getColumnName(i).trim().replace(".", ""), " ");
                } else {
                    properties.put(metaData.getColumnName(i).trim().replace(".", "").toLowerCase(), rs.getString(i));
                }
            }
            propList.add(properties);
        }
        close(conn, statement, rs);

        List<ListRowsRespVO> rows = new ArrayList<>();
        if (propList.size() > 0) {
            for (Map<String, Object> prop: propList) {
                ListRowsRespVO row = new ListRowsRespVO();
                rows.add((ListRowsRespVO) ReflectUtil.getTarget(row, prop));
            }
        }
        return rows;
    }

    private Connection getConnect(ApiDatasource datasource) throws Exception {
        Connection conn = JdbcUtil.getConnection(datasource);
        return conn;
    }

    private void close(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    private String getOrders(String datasourceId, String tableName, Map<String, String> columnMap) {
        if (StringUtils.isNull(datasourceId) || StringUtils.isNull(tableName)
                || StringUtils.isNull(columnMap)) {
            return null;
        }
        DataTypeDict matchedDict = null;
        List<DataTypeDict> dicts = dictMapper.list();
        for (DataTypeDict dict: dicts) {
            if (datasourceId.equalsIgnoreCase(dict.getDatasourceId())
                    && tableName.equalsIgnoreCase(dict.getTableName())) {
                matchedDict = dict;
            }
        }
        if (StringUtils.isNull(matchedDict)) {
            return null;
        }
        String orders = matchedDict.getOrders();
        if (StringUtils.isNull(orders)) {
            return null;
        }
        String[] orderColumns = orders.split(ORDER_COLUMNS_SPLIT_TOKEN);
        List<String> matchedOrderColumns = new ArrayList<>();
        for (int i = 0; i < orderColumns.length; i++) {
            if (columnMap.containsKey(orderColumns[i])) {
                matchedOrderColumns.add(orderColumns[i]);
            }
        }
        if (matchedOrderColumns.size() == 0) {
            return null;
        }
        return String.join(" desc" + ORDER_COLUMNS_SPLIT_TOKEN, matchedOrderColumns) + " desc";
    }
}
